package edu.cs.usask.ca.silver.persistence.sql;

/**
 * This interface contains all SQL strings necessary for creating the required
 * tables and constraints for Silver. By having them as separate strings they
 * can be executed individually.
 * 
 * Version ID is seeded from UUID class which uses a strong hash to generate
 * unique ids. The length of identity and version ID is always 36 CHAR.
 * 
 * all VARCHARs are converted to utf8 format.
 * 
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 * 
 */
public interface DerbySQLCreateTableStringsI {

	public static final String CREATE_TABLE_COMMENT = "CREATE TABLE Comment ("
			+ " c_ID			INT				GENERATED ALWAYS AS IDENTITY,"
			+ " c_changeset_ID INT			NOT NULL, "
			+ " c_author		VARCHAR(128)	NOT NULL, "
			+ " c_subject		VARCHAR(512)	NOT NULL, "
			+ " c_content		VARCHAR(1000)	NOT NULL, "
			+ " CONSTRAINT c_pkey PRIMARY KEY(c_ID), "
			+ " CONSTRAINT c_changeset_ID_fkey FOREIGN KEY(c_changeset_ID) REFERENCES ChangeSet(ch_ID) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_LOG_CHANGESET = "CREATE TABLE ChangeSet("
			+ " ch_ID				INT				GENERATED ALWAYS AS IDENTITY, "
			+ " ch_author_identity	CHAR(36)			NOT NULL, "
			+ " ch_author			VARCHAR(128)	NOT NULL, "
			+ " ch_change_time 		TIMESTAMP		NOT NULL, "
			+ " ch_from				CHAR(36)		NOT NULL, "
			+ " ch_to				CHAR(36)		NOT NULL, "
			+ " ch_entity_type 		VARCHAR(128)	NOT NULL, "
			+ " ch_description 		VARCHAR(1000)	NOT NULL, "
			+ " CONSTRAINT ch_pkey 	PRIMARY KEY (ch_ID), "
			+ " CONSTRAINT ch_author_identity_fkey FOREIGN KEY(ch_author_identity) REFERENCES SUser(u_identity) ON DELETE CASCADE ON UPDATE NO ACTION )";

	public static final String CREATE_TABLE_USER = "CREATE TABLE SUser ("
			+ "u_identity	   		CHAR(36)			NOT NULL,"
			+ "u_update_time   		TIMESTAMP	   		NOT NULL,"
			+ "u_first_name    		VARCHAR(128)		NOT NULL,"
			+ "u_last_name	   		VARCHAR(128)	   	NOT NULL,"
			+ "u_email         		VARCHAR(128)    	NOT NULL,"
			+ "u_pass          		VARCHAR(1000)   	NOT NULL,"
			+ "u_group_identity		CHAR(36),             "
			+ "u_creationDate  		TIMESTAMP       	NOT NULL,"
			+ "CONSTRAINT u_pkey 	PRIMARY KEY(u_identity))";

	public static final String CREATE_TABLE_GROUP = "CREATE TABLE SGroup ("
			+ "g_identity	   		CHAR(36)	   	NOT NULL,"
			+ "g_update_time   		TIMESTAMP	   	NOT NULL,"
			+ "g_name          		VARCHAR(512)    NOT NULL, "
			+ "g_owner_identity   	CHAR(36)		NOT NULL, "
			+ "g_privacy       		VARCHAR(32)     NOT NULL, "
			+ "g_creationDate  		TIMESTAMP	   	NOT NULL, "
			+ "g_description   		VARCHAR(1000)   NOT NULL, "
			+ "CONSTRAINT g_pkey 	PRIMARY KEY (g_identity),"
			+ "CONSTRAINT g_owner_identity_fkey FOREIGN KEY(g_owner_identity) REFERENCES SUser(u_identity) ON DELETE CASCADE ON UPDATE NO ACTION )";

	// The model project table keeps a counter mp_nextMvIdPerProject, that is
	// incremented with each version added to the project. This way each version
	// has a unique ID within a project in addition to the unique mv_id across
	// all
	// projects (since all versions, no matter which project, are stored in the
	// same
	// table - MVERSION.
	public static final String CREATE_TABLE_MPROJECT = "CREATE TABLE MProject ("
			+ "mp_identity					CHAR(36)		NOT NULL, "
			+ "mp_version					CHAR(36)		NOT NULL, "
			+ "mp_update_time				TIMESTAMP		NOT NULL, "
			+ "mp_name						VARCHAR(512)	NOT NULL, "
			+ "mp_creator					VARCHAR(128)	NOT NULL, "
			+ "mp_group_identity			CHAR(36)		NOT NULL, "
			+ "mp_type						VARCHAR(128)	NOT NULL, "
			+ "mp_creationDate				TIMESTAMP		NOT NULL, "
			+ "mp_description				VARCHAR(1000), "
			+ "CONSTRAINT mp_pkey PRIMARY KEY(mp_identity),"
			+ "CONSTRAINT mp_group_identity_fkey FOREIGN KEY(mp_group_identity) REFERENCES SGroup(g_identity)  ON DELETE CASCADE ON UPDATE NO ACTION )";

	// The visibility field is currently used as boolean with 1 being visible,
	// but
	// should be expanded to contain values similar to unix file system
	// permissions
	// (owner, group, world).
	public static final String CREATE_TABLE_MVERSION = "CREATE TABLE MVersion ( "
			+ "mv_identity				CHAR(36)		NOT NULL, "
			+ "mv_version				CHAR(36)		NOT NULL, "
			+ "mv_update_time			TIMESTAMP		NOT NULL, "
			+ "mv_name					VARCHAR(1000)	NOT NULL, "
			+ "mv_creator				VARCHAR(128)	NOT NULL, "
			+ "mv_type					VARCHAR(128)	NOT NULL, "
			+ "mv_creationDate			TIMESTAMP		NOT NULL, "
			+ "mv_description			VARCHAR(1000),"
			+ "mv_mp_identity			CHAR(36)		NOT NULL, "
			+ "mv_original_s			CHAR(36),"
			+ "mv_baseline_s			CHAR(36),"
			+ "mv_sourceFile			CHAR(36),"
			+ "mv_visibility			INT, "
			+ "CONSTRAINT mv_pkey PRIMARY KEY(mv_identity), "
			+ "CONSTRAINT mv_mp_identity_fkey FOREIGN KEY(mv_mp_identity) REFERENCES MProject(mp_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	// TODO - Add the ability for ScenarioCollections to be nested - i.e. add a
	// sc_parent_sc_id
	// attribute. If NULL, then the scenario collection is a top level
	// collection.
	public static final String CREATE_TABLE_SCENARIOCOLLECTION = "CREATE TABLE ScenarioCollection ( "
			+ "sc_identity				CHAR(36)		NOT NULL, "
			+ "sc_version				CHAR(36)		NOT NULL, "
			+ "sc_update_time			TIMESTAMP		NOT NULL, "
			+ "sc_name					VARCHAR(1000)	NOT NULL, "
			+ "sc_creator 				VARCHAR(128)   	NOT NULL, "
			+ "sc_creationDate			TIMESTAMP		NOT NULL, "
			+ "sc_description			VARCHAR(1000),"
			+ "sc_mv_identity			CHAR(36)		NOT NULL, "
			+ "CONSTRAINT sc_pkey PRIMARY KEY(sc_identity), "
			+ "CONSTRAINT sc_mv_identity_fkey FOREIGN KEY(sc_mv_identity) REFERENCES MVersion(mv_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_PARAMETER = "CREATE TABLE Parameter ( "
			+ "p_ID					INT 			NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
			+ "p_name				VARCHAR(512)	NOT NULL, "
			+ "p_type				VARCHAR(128)	NOT NULL, "
			+ "p_units				VARCHAR(50),"
			+ "p_class				VARCHAR(128)	NOT NULL,"
			+ "p_description		VARCHAR(1000), "
			+ "CONSTRAINT p_pkey 	PRIMARY KEY(p_ID))";

	public static final String CREATE_TABLE_PARAMETERSET = "CREATE TABLE ParameterSet ("
			+ "ps_ID				INT				NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
			+ "ps_description		VARCHAR(1000)	NOT NULL,"
			+ "CONSTRAINT ps_pkey 	PRIMARY KEY(ps_ID))";

	public static final String CREATE_TABLE_P2PS = "CREATE TABLE P2Ps ( "
			+ "p2ps_p_ID					INT 			NOT NULL, "
			+ "p2ps_ps_ID					INT 			NOT NULL, "
			+ "p2ps_value					VARCHAR(250)	NOT NULL,"
			+ "p2ps_comment					VARCHAR(1000),"
			+ "CONSTRAINT p2ps_pkey 		PRIMARY KEY(p2ps_p_ID, p2ps_ps_ID), "
			+ "CONSTRAINT p2ps_p_ID_fkey 	FOREIGN KEY(p2ps_p_ID) REFERENCES Parameter(p_ID) ON DELETE CASCADE ON UPDATE NO ACTION, "
			+ "CONSTRAINT p2ps_ps_ID_fkey 	FOREIGN KEY(p2ps_ps_ID) REFERENCES ParameterSet(ps_ID) ON DELETE CASCADE ON UPDATE NO ACTION )";

	public static final String CREATE_TABLE_SCENARIO = "CREATE TABLE Scenario ( "
			+ "s_identity		CHAR(36)		NOT NULL, "
			+ "s_version		CHAR(36)		NOT NULL, "
			+ "s_update_time	TIMESTAMP		NOT NULL, "
			+ "s_name			VARCHAR(1000)	NOT NULL, "
			+ "s_creator 		VARCHAR(128)   	NOT NULL, "
			+ "s_type			VARCHAR(128)	NOT NULL, "
			+ "s_creationDate	TIMESTAMP		NOT NULL, "
			+ "s_runStartTime	TIMESTAMP, "
			+ "s_runEndTime		TIMESTAMP, "
			+ "s_runUser		VARCHAR(512), "
			+ "s_description	VARCHAR(1000), "
			+ "s_mv_identity	CHAR(36)		NOT NULL, "
			+ "s_sc_identity	CHAR(36)		NOT NULL,"
			+ "s_ps_ID			INT				NOT NULL,"
			+ "CONSTRAINT s_pkey PRIMARY KEY(s_identity), "
			+ "CONSTRAINT s_mv_ID_fkey FOREIGN KEY(s_mv_identity) REFERENCES MVersion(mv_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT s_sc_ID_fkey FOREIGN KEY(s_sc_identity) REFERENCES ScenarioCollection(sc_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT s_ps_ID_fkey FOREIGN KEY(s_ps_ID) REFERENCES ParameterSet(ps_ID) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_FILE = "CREATE TABLE File ( "
			+ "f_identity				CHAR(36)			NOT NULL,"
			+ "f_name					VARCHAR(1000)		NOT NULL, "
			+ "f_description			VARCHAR(1000), " + "f_data					BLOB(1G), "
			+ "CONSTRAINT f_pkey 		PRIMARY KEY(f_identity))";

	public static final String CREATE_TABLE_INPUTFILES = "CREATE TABLE InputFiles ("
			+ "if_f_identity			CHAR(36)			NOT NULL,"
			+ "if_s_identity			CHAR(36)			NOT NULL,"
			+ "CONSTRAINT if_pkey 		PRIMARY KEY(if_s_identity, if_f_identity), "
			+ "CONSTRAINT if_s_ID_fkey 	FOREIGN KEY(if_s_identity) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION, "
			+ "CONSTRAINT if_f_ID_fkey 	FOREIGN KEY(if_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_TRAJECTORYFILES = "CREATE TABLE TrajectoryFiles ("
			+ "tf_f_identity			CHAR(36)			NOT NULL,"
			+ "tf_s_identity			CHAR(36)			NOT NULL,"
			+ "CONSTRAINT tf_pkey 		PRIMARY KEY(tf_s_identity, tf_f_identity), "
			+ "CONSTRAINT tf_s_ID_fkey 	FOREIGN KEY(tf_s_identity) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION, "
			+ "CONSTRAINT tf_f_ID_fkey 	FOREIGN KEY(tf_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_ANALYSISFILES = "CREATE TABLE AnalysisFiles ("
			+ "af_f_identity			CHAR(36)			NOT NULL,"
			+ "af_s_identity			CHAR(36)			NOT NULL,"
			+ "CONSTRAINT af_pkey 		PRIMARY KEY(af_s_identity, af_f_identity),"
			+ "CONSTRAINT af_s_ID_fkey 	FOREIGN KEY(af_s_identity) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT af_f_ID_fkey 	FOREIGN KEY(af_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_SC2F = "CREATE TABLE SC2F ("
			+ "sc2f_f_identity				CHAR(36)		NOT NULL,"
			+ "sc2f_sc_identity				CHAR(36)		NOT NULL,"
			+ "CONSTRAINT sc2f_pkey 		PRIMARY KEY(sc2f_f_identity, sc2f_sc_identity), "
			+ "CONSTRAINT sc2f_sc_ID_fkey 	FOREIGN KEY(sc2f_sc_identity) REFERENCES ScenarioCollection(sc_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT sc2f_f_ID_fkey 	FOREIGN KEY(sc2f_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_S2F = "CREATE TABLE S2F ("
			+ "s2f_f_identity			CHAR(36)		NOT NULL,"
			+ "s2f_s_identity			CHAR(36)		NOT NULL,"
			+ "CONSTRAINT s2f_pkey 		PRIMARY KEY(s2f_f_identity, s2f_s_identity), "
			+ "CONSTRAINT s2f_s_ID_fkey FOREIGN KEY(s2f_s_identity) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT s2f_f_ID_fkey FOREIGN KEY(s2f_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_MP2F = "CREATE TABLE MP2F ("
			+ "mp2f_f_identity				CHAR(36)		NOT NULL,"
			+ "mp2f_mp_identity				CHAR(36)		NOT NULL,"
			+ "CONSTRAINT mp2f_pkey 		PRIMARY KEY(mp2f_f_identity, mp2f_mp_identity), "
			+ "CONSTRAINT mp2f_mp_ID_fkey 	FOREIGN KEY(mp2f_mp_identity) REFERENCES MProject(mp_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT mp2f_f_ID_fkey 	FOREIGN KEY(mp2f_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_MV2F = "CREATE TABLE MV2F ("
			+ "mv2f_f_identity			CHAR(36)			NOT NULL,"
			+ "mv2f_mv_identity			CHAR(36)			NOT NULL,"
			+ "CONSTRAINT mv2f_pkey PRIMARY KEY(mv2f_f_identity, mv2f_mv_identity), "
			+ "CONSTRAINT mv2f_mv_ID_fkey FOREIGN KEY(mv2f_mv_identity) REFERENCES MVersion(mv_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT mv2f_f_ID_fkey FOREIGN KEY(mv2f_f_identity) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String CREATE_TABLE_U2GROUP = "CREATE TABLE U2Group ("
			+ "u2group_u_identity    		CHAR(36)			NOT NULL,"
			+ "u2group_g_identity			CHAR(36)            NOT NULL,"
			+ "u2group_join_time 			TIMESTAMP	   		NOT NULL,"
			+ "CONSTRAINT u2group_pkey 		PRIMARY KEY(u2group_u_identity, u2group_g_identity),"
			+ "CONSTRAINT u2group_u_ID_fkey FOREIGN KEY(u2group_u_identity) REFERENCES SUser(u_identity) ON DELETE CASCADE ON UPDATE NO ACTION,"
			+ "CONSTRAINT u2group_g_ID_fkey FOREIGN KEY(u2group_g_identity) REFERENCES SGroup(g_identity) ON DELETE CASCADE ON UPDATE NO ACTION)";

	public static final String ADDMVORIGINALSCENARIOCONSTRAINT = "ALTER TABLE MVersion ADD CONSTRAINT mv_original_s_fkey "
			+ "FOREIGN KEY(mv_original_s) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION";
	public static final String ADDMVBASELINESCENARIOCONSTRAINT = "ALTER TABLE MVersion ADD CONSTRAINT mv_baseline_s_fkey "
			+ "FOREIGN KEY(mv_baseline_s) REFERENCES Scenario(s_identity) ON DELETE CASCADE ON UPDATE NO ACTION";
	public static final String ADDMVSOURCEFILECONSTRAINT = "ALTER TABLE MVersion ADD CONSTRAINT mv_sourceFile_fkey "
			+ "FOREIGN KEY(mv_sourceFile) REFERENCES File(f_identity) ON DELETE CASCADE ON UPDATE NO ACTION";
	public static final String ADDUSERPRIVATEGROUPCONSTRAINT = "ALTER TABLE SUser ADD CONSTRAINT u_group_ID_fkey "
			+ "FOREIGN KEY(u_group_identity) REFERENCES SGroup(g_identity) ON DELETE CASCADE ON UPDATE NO ACTION";

	public static final String CREATE_DERBY_PROCEDURE_DUPLICATE_SCENARIO = "";
}
